﻿using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 低频天线阵列相控阵信号发生系统SmartFASK12.Models;
using Serilog;
using 低频天线阵列相控阵信号发生系统SmartFASK12.View;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Rebar;
using Log = Serilog.Log;

namespace 低频天线阵列相控阵信号发生系统SmartFASK12.Controls
{
    public class MExcel
    {
        private static MExcel _instance = null;

        private MExcel()
        {

        }

        public static MExcel Singleton()
        {
            if (_instance == null)
            {
                _instance = new MExcel();
            }
            return _instance;
        }

        /// <summary>
        /// 导出频带数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="frequencyBandTestings"></param>
        /// <returns></returns>
        public bool ToExportFrequencyBandData(string filePath, List<FrequencyBandTestingModel> frequencyBandTestings)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            try
            {
                XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("导出频带数据");
                XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0);
                XSSFCell newCel_1 = (XSSFCell)newRow_0.CreateCell(1);
                XSSFCell newCel_2 = (XSSFCell)newRow_0.CreateCell(2);
                XSSFCell newCel_3 = (XSSFCell)newRow_0.CreateCell(3);
                XSSFCell newCel_5 = (XSSFCell)newRow_0.CreateCell(5);
                newCel_0.SetCellValue("序号");
                newCel_1.SetCellValue("频率");
                newCel_2.SetCellValue("测得电压");
                newCel_3.SetCellValue("换算磁场");
                newCel_5.SetCellValue("电压值详情");
                for (int i = 0; i < frequencyBandTestings.Count; i++)
                {
                    XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i + 1);
                    XSSFCell newCel_i_0 = (XSSFCell)newRow.CreateCell(0);
                    XSSFCell newCel_i_1 = (XSSFCell)newRow.CreateCell(1);
                    XSSFCell newCel_i_2 = (XSSFCell)newRow.CreateCell(2);
                    XSSFCell newCel_i_3 = (XSSFCell)newRow.CreateCell(3);
                    newCel_i_0.SetCellValue(frequencyBandTestings[i].serialNumber);
                    newCel_i_1.SetCellValue(frequencyBandTestings[i].frequency);
                    newCel_i_2.SetCellValue(frequencyBandTestings[i].measuredVoltage);
                    newCel_i_3.SetCellValue(frequencyBandTestings[i].convertedMagneticField);

                    int row = 5;
                    foreach (double vlotage in frequencyBandTestings[i].measuredVoltages)
                    {
                        XSSFCell newCel_i_n = (XSSFCell)newRow.CreateCell(row);
                        newCel_i_n.SetCellValue(vlotage);
                        row++;
                    }
                }
                workBook.Write(new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite));
                workBook.Close();
                System.Diagnostics.Process.Start(@"" + filePath);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 导出通信距离
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="communicationsTests"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        internal bool ToExportCommunicationDistance(string filePath, List<CommunicationsTestModel> communicationsTests)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            try
            {
                XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("导出频带数据");
                XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0);
                XSSFCell newCel_1 = (XSSFCell)newRow_0.CreateCell(1);
                XSSFCell newCel_2 = (XSSFCell)newRow_0.CreateCell(2);
                newCel_0.SetCellValue("次数");
                newCel_1.SetCellValue("噪声");
                newCel_2.SetCellValue("信号");
                for (int i = 0; i < communicationsTests.Count; i++)
                {
                    XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i + 1);
                    XSSFCell newCel_i_0 = (XSSFCell)newRow.CreateCell(0);
                    XSSFCell newCel_i_1 = (XSSFCell)newRow.CreateCell(1);
                    XSSFCell newCel_i_2 = (XSSFCell)newRow.CreateCell(2);
                    newCel_i_0.SetCellValue(communicationsTests[i].frequency);
                    newCel_i_1.SetCellValue(communicationsTests[i].noise);
                    newCel_i_2.SetCellValue(communicationsTests[i].signal);
                }
                workBook.Write(new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite));
                workBook.Close();
                System.Diagnostics.Process.Start(@"" + filePath);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 导出磁场测量
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="magneticFieldShow"></param>
        /// <returns></returns>
        internal bool ToExportMagneticFieldMeasurement(string filePath, List<MagneticFieldModel> magneticFieldShow)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            try
            {
                XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("导出磁场测量");
                XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0);
                XSSFCell newCel_1 = (XSSFCell)newRow_0.CreateCell(1);
                XSSFCell newCel_2 = (XSSFCell)newRow_0.CreateCell(2);
                XSSFCell newCel_3 = (XSSFCell)newRow_0.CreateCell(3);
                newCel_0.SetCellValue("序号");
                newCel_1.SetCellValue("距离");
                newCel_2.SetCellValue("测得电压");
                newCel_3.SetCellValue("换算磁场");
                for (int i = 0; i < magneticFieldShow.Count; i++)
                {
                    XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i + 1);
                    XSSFCell newCel_i_0 = (XSSFCell)newRow.CreateCell(0);
                    XSSFCell newCel_i_1 = (XSSFCell)newRow.CreateCell(1);
                    XSSFCell newCel_i_2 = (XSSFCell)newRow.CreateCell(2);
                    XSSFCell newCel_i_3 = (XSSFCell)newRow.CreateCell(3);
                    newCel_i_0.SetCellValue(magneticFieldShow[i].serialNumber);
                    newCel_i_1.SetCellValue(magneticFieldShow[i].distance);
                    newCel_i_2.SetCellValue(magneticFieldShow[i].measuredVoltage);
                    newCel_i_3.SetCellValue(magneticFieldShow[i].convertedMagneticField);
                }
                workBook.Write(new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite));
                workBook.Close();
                System.Diagnostics.Process.Start(@"" + filePath);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 导出数据测量
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="selectedIndex">0-4431 1-6218</param>
        /// <param name="_6218ResultModel"></param>
        /// <param name="_4431ResultModel"></param>
        /// <returns></returns>
        internal bool ToExportDataMeasurement(string filePath, int selectedIndex, _6218ResultModel _6218ResultModel, _4431ResultModel _4431ResultModel)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet newSheet;
            FileStream fileStream = null;
            int count_1 = 0;
            try
            {
                //4431
                if (selectedIndex == 0)
                {
                    //行数
                    int rowCount = _4431ResultModel.xs.Count / _4431ResultModel.physicalChannelCount;
                    //页数
                    int createSheetCount = (int)Math.Ceiling(rowCount / 1000000.0);
                    //100w行
                    for (int createSheet = 0; createSheet < createSheetCount; createSheet++)
                    {
                        newSheet = (XSSFSheet)workBook.CreateSheet("导出4431数据" + createSheet);
                        XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                        for (int i = 0; i < _4431ResultModel.physicalChannelCount; i++)
                        {
                            XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0 + i * 4);
                            XSSFCell newCel_1 = (XSSFCell)newRow_0.CreateCell(1 + i * 4);
                            XSSFCell newCel_2 = (XSSFCell)newRow_0.CreateCell(2 + i * 4);
                            XSSFCell newCel_3 = (XSSFCell)newRow_0.CreateCell(3 + i * 4);
                            newCel_0.SetCellValue("实时数据x" + (i + 1));
                            newCel_1.SetCellValue("实时数据y" + (i + 1));
                            newCel_2.SetCellValue("FFTx" + (i + 1));
                            newCel_3.SetCellValue("FFTy" + (i + 1));
                        }

                        //一页行数
                        int onePageNumberRows = 0;
                        if (rowCount >= 1000000 * (createSheet + 1))
                        {
                            onePageNumberRows = 1000000;// * (createSheet + 1);
                        }
                        else
                        {
                            onePageNumberRows = rowCount - 1000000 * createSheet;
                        }
                        for (int i = 0; i < onePageNumberRows; i++)
                        {
                            XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i + 1);
                            for (int k = 0; k < _4431ResultModel.physicalChannelCount; k++)
                            {
                                int row = k * _4431ResultModel.samplesPerChannel + i + 1000000 * createSheet;

                                count_1 = row;
                                XSSFCell newCel_i_0 = (XSSFCell)newRow.CreateCell(0 + k * 4);
                                XSSFCell newCel_i_1 = (XSSFCell)newRow.CreateCell(1 + k * 4);
                                newCel_i_0.SetCellValue(_4431ResultModel.xs[row]);
                                newCel_i_1.SetCellValue(_4431ResultModel.ys[row]);
                                if ((_4431ResultModel.fftxs.Count / _4431ResultModel.physicalChannelCount) - (1000000 * createSheet) / 2 > i)
                                {
                                    int row1 = k * _4431ResultModel.samplesPerChannel / 2 + i + (1000000 * createSheet) / 2;
                                    XSSFCell newCel_i_2 = (XSSFCell)newRow.CreateCell(2 + k * 4);
                                    XSSFCell newCel_i_3 = (XSSFCell)newRow.CreateCell(3 + k * 4);
                                    newCel_i_2.SetCellValue(_4431ResultModel.fftxs[row1]);
                                    newCel_i_3.SetCellValue(_4431ResultModel.fftys[row1]);
                                }
                            }
                        }
                    }
                }
                //6218
                else
                {
                    newSheet = (XSSFSheet)workBook.CreateSheet("导出6218数据");
                    XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                    XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0);
                    XSSFCell newCel_1 = (XSSFCell)newRow_0.CreateCell(1);
                    XSSFCell newCel_2 = (XSSFCell)newRow_0.CreateCell(2);
                    XSSFCell newCel_3 = (XSSFCell)newRow_0.CreateCell(3);
                    newCel_0.SetCellValue("实时数据x");
                    newCel_1.SetCellValue("实时数据y");
                    newCel_2.SetCellValue("FFTx");
                    newCel_3.SetCellValue("FFTy");
                    for (int i = 0; i < _6218ResultModel.xs.Count; i++)
                    {
                        XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i + 1);
                        XSSFCell newCel_i_0 = (XSSFCell)newRow.CreateCell(0);
                        XSSFCell newCel_i_1 = (XSSFCell)newRow.CreateCell(1);
                        newCel_i_0.SetCellValue(_6218ResultModel.xs[i]);
                        newCel_i_1.SetCellValue(_6218ResultModel.ys[i]);
                        if (_6218ResultModel.fftxs.Count > i)
                        {
                            XSSFCell newCel_i_2 = (XSSFCell)newRow.CreateCell(2);
                            XSSFCell newCel_i_3 = (XSSFCell)newRow.CreateCell(3);
                            newCel_i_2.SetCellValue(_6218ResultModel.fftxs[i]);
                            newCel_i_3.SetCellValue(_6218ResultModel.fftys[i]);
                        }
                    }
                }

                fileStream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
                workBook.Write(fileStream);
                newSheet = null;
                workBook.Close();
                System.Diagnostics.Process.Start(@"" + filePath);
                return true;
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message + count_1);
                workBook.Close();
                return false;
            }
            finally
            {
                fileStream?.Close();
                fileStream?.Dispose();
                GC.Collect();
            }
        }

        /// <summary>
        /// 读取采集信息
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public _4431ResultModel ReadCollectionInfo(string filePath)
        {
            _4431ResultModel _4431ResultModel = new _4431ResultModel();
            IWorkbook workbook = null;
            try
            {
                FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                workbook = new XSSFWorkbook(file);

                ISheet sheet = workbook.GetSheetAt(0);

                _4431ResultModel.physicalChannelCount = sheet.GetRow(1).LastCellNum / 4;
                _4431ResultModel.samplesPerChannel = sheet.LastRowNum;
                for (int k = 0; k < _4431ResultModel.physicalChannelCount; k++)
                {
                    // 遍历工作表中的行
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            // 遍历行中的单元格
                            for (int j = 4 * k; j < 4 * (k + 1); j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell != null)
                                {
                                    if (j % 4 == 0)
                                    {
                                        _4431ResultModel.xs.Add(cell.NumericCellValue);
                                    }
                                    else if (j % 4 == 1)
                                    {
                                        _4431ResultModel.ys.Add(cell.NumericCellValue);
                                    }
                                    else if (j % 4 == 2)
                                    {
                                        _4431ResultModel.fftxs.Add(cell.NumericCellValue);
                                    }
                                    else if (j % 4 == 3)
                                    {
                                        _4431ResultModel.fftys.Add(cell.NumericCellValue);
                                    }
                                }
                            }
                        }
                    }
                }

                // 关闭文件流和工作簿
                file.Close();
                workbook.Close();
                return _4431ResultModel;
            }
            catch (Exception ex)
            {
                return new _4431ResultModel();
            }
        }

        /// <summary>
        /// NPOI导出Excel
        /// </summary>
        /// <returns></returns>
        public bool ToExcel_NPOI(double[] _yvalues, string filePath)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            try
            {
                XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("导出数据");

                XSSFRow newRow_0 = (XSSFRow)newSheet.CreateRow(0);
                XSSFCell newCel_0 = (XSSFCell)newRow_0.CreateCell(0);
                newCel_0.SetCellValue("采集到的数据");

                int x = 0;
                double cx = 0;
                double interval = 1.0 / MConvert.Singleton().KToInt(MPublic.ConfigModel2.samplesPerBuffer_gather);

                foreach (double y in _yvalues)
                {
                    XSSFRow newRow = (XSSFRow)newSheet.CreateRow(x + 1);
                    XSSFCell cell_y = (XSSFCell)newRow.CreateCell(1);
                    XSSFCell cell_x = (XSSFCell)newRow.CreateCell(0);
                    cell_y.SetCellValue(y);
                    cell_x.SetCellValue(cx);
                    cx += interval;
                    x++;
                }

                workBook.Write(new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite));
                workBook.Close();
                System.Diagnostics.Process.Start(@"" + filePath);
                return true;
            }
            catch (Exception ex)
            {
                workBook.Close();
                Log.Error(ex, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 读取导入数据
        /// </summary>
        /// <returns></returns>
        public List<double> Excel_ReadImportData_NPOI()
        {
            List<double> data = new List<double>();
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(@"F:\360MoveData\Users\zy\Desktop\新建文件夹 (2)\2m,2V,2.81k-1.81k,40kHz两秒.xlsx");

                ISheet sheet = workbook.GetSheetAt(0);

                for (int f = 1; f < 250000; f++)
                {
                    IRow row = sheet.GetRow(f);
                    if (row == null)
                        break;
                    NPOI.SS.UserModel.ICell cell = row.GetCell(0);
                    if (cell == null)
                        break;
                    data.Add(cell.NumericCellValue);
                }
                workbook.Close();
                return data;
            }
            catch (Exception ex)
            {
                workbook?.Close();
                Log.Error(ex, ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 读取导入数据
        /// </summary>
        /// <returns></returns>
        public List<double> Excel_Import_NPOI(string path)
        {
            List<double> data = new List<double>();
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(path);

                ISheet sheet = workbook.GetSheetAt(0);

                for (int f = 8; f < sheet.LastRowNum + 1; f++)
                {
                    IRow row = sheet.GetRow(f);
                    if (row == null)
                        break;
                    NPOI.SS.UserModel.ICell cell = row.GetCell(1);
                    if (cell == null)
                        break;
                    data.Add(cell.NumericCellValue);
                }
                workbook.Close();
                return data;
            }
            catch (Exception ex)
            {
                workbook?.Close();
                Log.Error(ex, ex.Message);
                return null;
            }
        }
    }
}
